""""
Case Type   : D库常用语法兼容-proc
Case Name   : D库支持创建存储过程。验证重载功能正常报错
Create At   : 2025-11-19
Owner       : chen
Description :
    1.创建type
    2.创建存储过程
    3.调用存储过程
    4.修改s_type
    5.proc重载
    6.procedure重载
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.合理报错
    6.合理报错
    7.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_Proc_Case0006(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.com = Common()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.proc_name = 'p_proc_case006'
        self.type_name = 'e_proc_type_case006'
        self.param_options = 'behavior_compat_options'
        self.def_param_options = self.com.show_param(self.param_options)
        self.shark = self.com.show_param('shared_preload_libraries')

    def test_proc(self):
        text = '-----step1: 创建type; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_type = f'''drop type if exists {self.type_name};
            create type {self.type_name} as (
                id integer,
                name varchar(50),
            address varchar(100)
            );'''
        res = primary_sh.execut_db_sql(create_type, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, res)

        text = '-----step2: 创建存储过程; expect: 成功-----'
        log.info(text)
        if 'shark' not in self.shark:
            set_param = primary_sh.execute_gsguc('set',
                                             self.constant.GSGUC_SUCCESS_MSG,
                                             f"shared_preload_libraries = 'shark'")
            log.info(set_param)
            self.assertTrue(set_param, '参数修改失败')
        set_param = primary_sh.execute_gsguc('set',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f"{self.param_options} = ''")
        log.info(set_param)
        self.assertTrue(set_param, '参数修改失败')
        res = primary_sh.restart_db_cluster()
        self.assertTrue(res)
        create_proc = f'''
            create or replace procedure {self.proc_name}(a {self.type_name})
            is
            begin
            RAISE INFO 'call a: %', a;
            end;'''
        res = primary_sh.execut_db_sql(create_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)

        text = '-----step3: 调用存储过程; expect: 成功-----'
        log.info(text)
        call_sql = f"call {self.proc_name}(((1,'zhang','shanghai')));"
        res = primary_sh.execut_db_sql(call_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('INFO:  call a: (1,zhang,shanghai)', res)
        self.assertIn('(1 row)', res)

        text = '-----step4: 修改s_type; expect: 成功-----'
        log.info(text)
        alter_sql = f'alter type {self.type_name} ADD attribute a int;' \
            f"call {self.proc_name}(((1,'zhang','shanghai', 10)));"
        res = primary_sh.execut_db_sql(alter_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.ALTER_TYPE_SUCCESS_MSG, res)
        self.assertIn('INFO:  call a: (1,zhang,shanghai,10)', res)
        self.assertIn('(1 row)', res)

        text = '-----step5: proc重载; expect: 成功-----'
        log.info(text)
        alter_sql = f'alter proc {self.proc_name}({self.type_name}) compile;'
        res = primary_sh.execut_db_sql(alter_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('ERROR:  This operation is not supported.', res)

        text = '-----step6: procedure重载; expect: 成功-----'
        log.info(text)
        alter_sql = f'alter procedure {self.proc_name}({self.type_name}) compile;'
        res = primary_sh.execut_db_sql(alter_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('ERROR:  This operation is not supported.', res)

    def tearDown(self):
        text = '-----step7: 清理环境; expect: 成功-----'
        log.info(text)
        drop_proc = f'''drop proc if exists {self.proc_name};'''
        res = primary_sh.execut_db_sql(drop_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG, res)
        drop_type = f'drop type if exists {self.type_name};'
        res = primary_sh.execut_db_sql(drop_type, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_TYPE_SUCCESS_MSG, res)
        set_param = primary_sh.execute_gsguc('set',
                        self.constant.GSGUC_SUCCESS_MSG,
                        f"{self.param_options} = '{self.def_param_options}'")
        log.info(set_param)
        self.assertTrue(set_param, '参数修改失败')
        res = primary_sh.restart_db_cluster()
        self.assertTrue(res)